package faceattendsystem.daoImpl;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import faceattendsystem.dao.IMechanismDao;
import faceattendsystem.entity.Mechanism;
import faceattendsystem.entity.PageBean;
import faceattendsystem.util.JDBCTemplate;
import faceattendsystem.util.JDBCTemplate.PreparedStatementSetter;
import faceattendsystem.util.JDBCTemplate.ResultSetCallBack;


public class MechanismDaoImpl implements IMechanismDao{

	@Override
	public int add(Mechanism mechanism) {
		String sql = "insert into mechanism values(?,?,?,?)";
		
		int result = JDBCTemplate.update(sql, new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement pstmt) throws SQLException {
				pstmt.setString(1, mechanism.getMechid());
				pstmt.setString(2, mechanism.getMechname());
				pstmt.setString(3, mechanism.getMechip());
				pstmt.setFloat(4, mechanism.getMinsimilarity());
			}
		});
		
		return result;
	}

	@Override
	public int delete(String mechid) {
		String sql = "delete from mechanism where mechid=?";
		
		int result = JDBCTemplate.update(sql, new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement pstmt) throws SQLException {
				pstmt.setString(1, mechid);
			}
		});
		
		return result;
	}
	
	@Override
	public int delete() {
		String sql = "truncate table mechanism";
		return JDBCTemplate.update(sql, null);
	}

	@Override
	public int update(Mechanism mechanism) {
		String sql = "update mechanism set mechname=?,mechip=?,minsimilarity=? where mechid=?";
		
		int result = JDBCTemplate.update(sql, new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement pstmt) throws SQLException {
				pstmt.setString(1, mechanism.getMechname());
				pstmt.setString(2, mechanism.getMechip());
				pstmt.setFloat(3, mechanism.getMinsimilarity());
				pstmt.setString(4, mechanism.getMechid());
			}
		});
		
		return result;
	}
	
	@Override
	public Mechanism queryByMechname(String mechname) {
		String sql = "select * from mechanism where mechname=?";
		
		Mechanism mechanism = JDBCTemplate.singleQuery(sql, new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement pstmt) throws SQLException {
				pstmt.setString(1, mechname);
			}
		}, new ResultSetCallBack<Mechanism>() {
			@Override
			public Mechanism processRs(ResultSet rs) throws SQLException {
				Mechanism mechanism = new Mechanism();
				mechanism.setMechid(rs.getString(1));
				mechanism.setMechname(rs.getString(2));
				mechanism.setMechip(rs.getString(3));
				mechanism.setMinsimilarity(rs.getFloat(4));
				return mechanism;
			}
		});
		
		return mechanism;
	}
	
	@Override
	public PageBean<Mechanism> query(Mechanism mechanism,int pc, int ps) {
		PageBean<Mechanism> pb = new PageBean<Mechanism>();
		pb.setPc(pc);
		pb.setPs(ps);
		
		String mechid = mechanism.getMechid();
		String mechname = mechanism.getMechname();
		String mechip = mechanism.getMechip();
		String minsimilarity = String.valueOf(mechanism.getMinsimilarity());
		if(minsimilarity.equals("0.0")){
			minsimilarity = "0";
		}
		
		String sql = "select count(*) from mechanism where mechid like '%"+mechid+"%' and mechname like '%"+mechname+"%' and mechip like '%"+mechip+"%' and minsimilarity like '%"+minsimilarity+"%'";
		
		Number num = JDBCTemplate.singleQuery(sql, null, new ResultSetCallBack<Number>() {
			@Override
			public Number processRs(ResultSet rs) throws SQLException {
				return rs.getInt(1);
			}
		});
		int tr = 1;
		if(num!=null){
			tr=num.intValue();
		}
		pb.setTr(tr);
		
		sql = "select * from mechanism where mechid like '%"+mechid+"%' and mechname like '%"+mechname+"%' and mechip like '%"+mechip+"%' and minsimilarity like '%"+minsimilarity+"%' order by mechid asc limit ?,?";
		List<Mechanism> mechanismList = JDBCTemplate.query(sql, new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement pstmt) throws SQLException {
				pstmt.setInt(1, (pc-1)*ps);
				pstmt.setInt(2, ps);
			}
		}, new ResultSetCallBack<Mechanism>() {
			@Override
			public Mechanism processRs(ResultSet rs) throws SQLException {
				Mechanism mechanism = new Mechanism();
				mechanism.setMechid(rs.getString(1));
				mechanism.setMechname(rs.getString(2));
				mechanism.setMechip(rs.getString(3));
				mechanism.setMinsimilarity(rs.getFloat(4));
				return mechanism;
			}
		});
		
		pb.setBeanList(mechanismList);
		return pb;
	}

	@Override
	public List<Mechanism> queryAll() {
		String sql = "select * from mechanism order by mechid";
		
		List<Mechanism> mechanismList = JDBCTemplate.query(sql, null, new ResultSetCallBack<Mechanism>() {
			@Override
			public Mechanism processRs(ResultSet rs) throws SQLException {
				Mechanism mechanism = new Mechanism();
				mechanism.setMechid(rs.getString(1));
				mechanism.setMechname(rs.getString(2));
				mechanism.setMechip(rs.getString(3));
				mechanism.setMinsimilarity(rs.getFloat(4));
				return mechanism;
			}
		});
		
		return mechanismList;
	}

	@Override
	public Mechanism queryByMechid(String mechid) {
		String sql = "select * from mechanism where mechid=?";
		
		Mechanism mechanism = JDBCTemplate.singleQuery(sql, new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement pstmt) throws SQLException {
				pstmt.setString(1, mechid);
			}
		}, new ResultSetCallBack<Mechanism>() {
			@Override
			public Mechanism processRs(ResultSet rs) throws SQLException {
				Mechanism mechanism = new Mechanism();
				mechanism.setMechid(rs.getString(1));
				mechanism.setMechname(rs.getString(2));
				mechanism.setMechip(rs.getString(3));
				mechanism.setMinsimilarity(rs.getFloat(4));
				return mechanism;
			}
		});
		
		return mechanism;
	}
}
